from flask import Blueprint, request, jsonify, send_file, current_app
import os
import duckdb
import pandas as pd
import time
import uuid, json, traceback
from datetime import datetime
from config import ini_op
from utils.path_utils import get_project_root
from database.duckdb.duckdb_base import get_connection

# app = Flask(__name__)
file_upload_blueprint = Blueprint('file_upload', __name__)


def get_upload_folder(p_folder='uploads'):

    # 设置上传文件夹路径
    UPLOAD_FOLDER = os.path.join(get_project_root(), p_folder)

    if not os.path.exists(UPLOAD_FOLDER):
        os.makedirs(UPLOAD_FOLDER)

    return UPLOAD_FOLDER

# 获取模板文件路径
def get_file_folder(p_folder='file'):

    # 设置上传文件夹路径
    UPLOAD_FOLDER = os.path.join(get_project_root(), p_folder)

    if not os.path.exists(UPLOAD_FOLDER):
        os.makedirs(UPLOAD_FOLDER)

    return UPLOAD_FOLDER



@file_upload_blueprint.route('/init')
def init():
    try:
        con = get_connection()
        # 创建表来存储上传记录
        con.execute("""
        CREATE OR REPLACE TABLE uploads (
            id VARCHAR(36) PRIMARY KEY,
            filename VARCHAR(255),
            unique_filename VARCHAR(128),
            folder_path VARCHAR(255),
            file_type VARCHAR(128),
            file_content_type VARCHAR(128), 
            file_content_type_name VARCHAR(128),
            upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            analyse_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """)
        return jsonify({"code": 200, "message": "操作完成"}), 200
    except Exception as e:
        return jsonify({"code": 500, "error": str(e)}), 500


@file_upload_blueprint.route('/showlog')
def showlog():
    con = get_connection()
    query = "SELECT * FROM uploads"
    result = con.execute(query).fetchall()
    con.close()
    return result


# 获取分页记录
@file_upload_blueprint.route('/api/pagedata', methods=['POST'])
def get_pagedata_controller():
    try:
        data = request.get_json()
        pageNum = data.get('pageNum')
        pageSize = data.get('pageSize')
        data, total_count = get_pagedata( pageNum, pageSize)
        return jsonify({"code":200,"pageNum":pageNum,"pageSize":pageSize,"total":total_count, "data":data}), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500

def get_pagedata(p_page = 1, p_pageSize = 10):

    upload_folder = get_upload_folder()
    # 计算分页查询的起始位置
    l_offset = (p_page - 1) * p_pageSize


    con = get_connection()
    result = con.execute("SELECT id, filename, unique_filename, folder_path, file_type, file_content_type_name,  "
                         " strftime(upload_time, '%Y-%m-%d %H:%M:%S') upload_time,"
                         " strftime(analyse_time, '%Y-%m-%d %H:%M:%S') analyse_time,"
                         " $upload_folder||'\\'|| folder_path||'\\'||unique_filename file_url"
                         " FROM uploads "
                         " ORDER BY upload_time desc"
                         " LIMIT $p_pageSize OFFSET $p_offset ",
                         {'p_pageSize':p_pageSize, 'p_offset':l_offset, 'upload_folder':upload_folder}).fetchall()

    # 获取表头（列名）
    columns = [col[0] for col in con.description]

    # 将结果转换为字典列表（键值对）
    data = [dict(zip(columns, row)) for row in result]

    # 获取数据总条数
    total_count = con.execute("SELECT COUNT(*) FROM uploads ").fetchone()[0]

    con.close()
    return [data, total_count]


@file_upload_blueprint.route('/api/download_record/<file_path>', methods=['GET'])
def download_record_get(file_path):
    return send_file(file_path, as_attachment=True)

@file_upload_blueprint.route('/api/download_record', methods=['POST'])
def download_record():
    try:
        data = request.get_json()
        fileid = data.get('fileid')
        con = get_connection()
        result = con.execute("SELECT filename, folder_path, unique_filename "
                             " FROM uploads "
                             " where id = $fileid",
                             {'fileid': fileid}).fetchone()

        filename = result[0]
        folder_path = result[1]
        unique_filename = result[2]
        file_path = os.path.join(get_upload_folder(), folder_path, unique_filename)
        print(file_path)
        if not os.path.exists(file_path):
            return jsonify({'error': '文件不存在'}), 404
        return send_file(file_path, as_attachment=True, download_name=filename)
    except Exception as e:
        # 处理可能的错误
        return jsonify({'error': str(e)}), 500

@file_upload_blueprint.route('/api/download_file', methods=['POST'])
def download_file():
    try:
        data = request.get_json()
        folder_path = data.get('filePath')
        filename = data.get('fileName')
        file_path = os.path.join(get_file_folder(), folder_path, filename)
        return send_file(file_path, as_attachment=True, download_name=filename)
    except Exception as e:
        # 处理可能的错误
        return jsonify({'error': str(e)}), 500

def allowed_file(filename):
    ALLOWED_EXTENSIONS = {'xlsx', 'xlsm'}
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS


def get_excel_check_schema():
    """
    获取文件内容判断配置
    """
    json_file_path = os.path.join(get_file_folder(), 'ExcelCheckSchema.json')
    try:
        # 使用 with 语句自动管理文件的打开和关闭
        with open(json_file_path, 'r', encoding='utf-8') as file:
            # 加载并解析JSON数据
            data = json.load(file)

            # 现在你可以操作解析后的数据了
            # print(data)  # 打印整个数据结构
            return data

    except Exception as e:
        print(f"文件 {json_file_path} 未找到.")
        return [
    {
        "type": "PZ",
        "type_name": "凭证",
        "required_sheets": [
            {
                "sheet": "凭证头",
                "headers": [
                    "凭证ID",
                    "凭证编号",
                    "凭证日期",
                    "凭证类型编号",
                    "会计主管编号",
                    "核算组织编号",
                    "会计期间序号",
                    "附件张数",
                    "核算账簿编号",
                    "制单人编号",
                    "经办人编号",
                    "密级",
                    "密级名称"
                ]
            },
            {
                "sheet": "凭证分录"
            },
            {
                "sheet": "凭证辅助"
            }
        ]
    },
    {
        "type": "KM",
        "type_name": "科目字典",
        "required_sheets": [
            {
                "sheet": "会计科目"
            }
        ]
    },
    {
        "type": "FZNC",
        "type_name": "项目年初",
        "required_sheets": [
            {
                "sheet": "项目余额表"
            }
        ]
    },
    {
        "type": "KMNC",
        "type_name": "科目年初",
        "required_sheets": [
            {
                "sheet": "科目余额表"
            }
        ]
    },
    {
        "type": "FYMC",
        "type_name": "报表列配置",
        "require_headers": {
            "headers_row": 1,
            "headers": [
                "模型",
                "列编号",
                "列名称",
                "会计年度",
                "数据描述",
                "备注"
            ]
        }
    },
    {
        "type": "PROJECT",
        "type_name": "型号配置",
        "require_headers": {
            "headers_row": 1,
            "headers": [
                "模型",
                "类型",
                "型号编号",
                "型号名称",
                "主业项目编号",
                "备注"
            ]
        }
    }
]


def check_file_content_type(file_path):
    """
    根据文件内容，判断文件业务数据类型
    """
    excel_file = pd.ExcelFile(file_path)
    # 使用 .sheet_names 属性获取所有 sheet 的名称列表
    file_sheet_names = excel_file.sheet_names
    # print("file_sheet_names:", file_sheet_names)
    excel_file.close()
    required_sheets_rule = get_excel_check_schema()

    try:
        for item in required_sheets_rule:
            if 'required_sheets' in item:
                for required_sheets in item["required_sheets"]:
                    # print("required_sheets:", required_sheets["sheet"])
                    if required_sheets["sheet"] not in file_sheet_names:
                        break
                    elif 'headers' in required_sheets:
                        # print("headers:", required_sheets["headers"])

                        df = pd.read_excel(file_path, sheet_name=required_sheets["sheet"])

                        header = df.columns.tolist()
                        if "headers_row" in required_sheets and required_sheets['headers_row'] - 2 < len(df) and \
                                required_sheets['headers_row'] != 1:
                            headers_row = required_sheets['headers_row']
                            row_n = df.iloc[headers_row - 2]
                            header = row_n.tolist()

                        # print(111)
                        # print("df:",df)
                        # print("header:",header)
                        if all(column in header for column in required_sheets["headers"]):
                            return item['type'], item['type_name']
                        else:
                            continue
                    else:
                        return item['type'], item['type_name']
            elif 'require_headers' in item:
                require_headers = item['require_headers']
                df = pd.read_excel(file_path)
                header = df.columns.tolist()
                if "headers_row" in require_headers and require_headers['headers_row'] - 2 < len(df) and \
                        require_headers['headers_row'] != 1:
                    headers_row = require_headers['headers_row']
                    row_n = df.iloc[headers_row - 2]
                    header = row_n.tolist()
                # print("df:",df)
                if 'headers' in require_headers and all(column in header for column in require_headers['headers']):
                    return item['type'], item['type_name']

    except Exception as e:
        print(e)
        return jsonify(str(e)), jsonify(str(e))
    return None, None

@file_upload_blueprint.route('/api/load_content_data', methods=['POST'])
def controller_load_content_data():
    try:
        data = request.json
        fileid = data.get('fileid')

        con = get_connection()
        result = con.execute("SELECT filename, folder_path, unique_filename, file_content_type, file_content_type_name "
                             " FROM uploads "
                             " where id = $fileid",
                             {'fileid': fileid}).fetchone()


        filename = result[0]
        folder_path = result[1]
        unique_filename = result[2]
        file_path = os.path.join(get_upload_folder(), folder_path, unique_filename)
        file_content_type = result[3]
        code = load_content_data(file_path, file_content_type, fileid)
        if code == 401 or code == 400:
            return jsonify({'code': 400, 'message': '文件内容识别失败'}), 400
        else:
            return jsonify({'code': 200, 'message': '数据加载完成'}), 200
    except Exception as e:
        return jsonify({'code': 500, 'message': str(e)}), 500


def load_content_data(file_path, file_content_type, drlx, id=None):
    code = 200
    if file_content_type == 'PROJECT':
        from service.nw_project.nw_project_service import load_file_new as load_file

        dwbh, model, qzq_type = ini_op.getinivalue('basic_config', 'dwbh', 'model', 'qzq_type')
        code = load_file(dwbh, model, qzq_type, file_path)
    elif file_content_type == 'FYMC':
        from service.nw_fymc.nw_fymc_service import load_file
        dwbh, model = ini_op.getinivalue('basic_config', 'dwbh', 'model')
        code = load_file(dwbh, model, file_path)
    elif file_content_type == 'PZ':
        from service.sjcl import sjcl_pz
        dwbh, dwid, zbid, zbbh = ini_op.getinivalue('basic_config', 'dwbh', 'dwid', 'ledger', 'zbbh')
        code = sjcl_pz.run(dwbh, dwid, zbid, zbbh, '', '', file_path, str(time.strftime("%Y%m%d%H%M%S")), 'sd')
    elif file_content_type == 'KM':
        from service.sjcl import sjcl_kmzd
        dwbh, dwid, kmtx_id, zbbh, kjnd = ini_op.getinivalue('basic_config', 'dwbh', 'dwid', 'kmtx_id', 'zbbh', 'kjnd')
        if drlx == 'zd':
            kjnd = ini_op.getinivalue('kmzd_config', 'kjnd')[0]
        sjcl_kmzd.run(dwbh, dwid, kmtx_id, zbbh, kjnd, file_path, str(time.strftime("%Y%m%d%H%M%S")))
    elif file_content_type == 'XM':
        from service.sjcl import sjcl_xmzd
        dwbh, dwid, zbbh, kjnd = ini_op.getinivalue('basic_config', 'dwbh', 'dwid', 'zbbh', 'kjnd')
        if drlx == 'zd':
            kjnd = ini_op.getinivalue('xmzd_config', 'nd')[0]
        sjcl_xmzd.run(dwbh, dwid, zbbh, kjnd, file_path, str(time.strftime("%Y%m%d%H%M%S")))
    elif file_content_type == 'FZNC':
        from service.sjcl import sjcl_xmyeb
        dwbh, zbid, zbbh = ini_op.getinivalue('basic_config', 'dwbh', 'ledger', 'zbbh')
        sjcl_xmyeb.run(dwbh, zbid, zbbh, '', file_path, str(time.strftime("%Y%m%d%H%M%S")))
    elif file_content_type == 'KMNC':
        from service.sjcl import sjcl_kmyeb
        dwbh, zbid, zbbh = ini_op.getinivalue('basic_config', 'dwbh', 'ledger', 'zbbh')
        sjcl_kmyeb.run(dwbh, zbid, zbbh, '', file_path, str(time.strftime("%Y%m%d%H%M%S")))
    elif file_content_type == 'WLDW':
        from service.sjcl import sjcl_wldw
        sjcl_wldw.run(file_path, str(time.strftime("%Y%m%d%H%M%S")))
    elif file_content_type == 'BMZD':
        from service.sjcl import sjcl_bmzd
        sjcl_bmzd.run(file_path, str(time.strftime("%Y%m%d%H%M%S")))
    else:
        code = 401
    if id is not None:
        con = get_connection()
        # 更新数据分析时间
        con.execute("update uploads set analyse_time = CURRENT_TIMESTAMP where id = $id",{'id': id})
        con.close()

    return code

@file_upload_blueprint.route('/upload', methods=['POST'])
def upload_file():
    try:
        if 'file' not in request.files:
            return jsonify('未识别到文件'), 400
        file = request.files['file']
        if file.filename == '':
            return jsonify('未识别到文件'), 400
        if file and allowed_file(file.filename):
            upload_folder = 'uploads'
            # 判断文件类型并创建对应的文件夹
            file_extension = file.filename.rsplit('.', 1)[1].lower()

            # 获取当前日期并创建对应的文件夹
            current_date = datetime.now().strftime('%Y%m%d')
            relatively_folder_path = os.path.join(current_date, file_extension)
            folder_path = get_upload_folder(os.path.join(upload_folder, relatively_folder_path))
            if not os.path.exists(folder_path):
                os.makedirs(folder_path)

            uuid4 = uuid.uuid4()
            # 生成唯一的文件名
            unique_filename = f"{uuid4}.{file_extension}"

            file_path = os.path.join(folder_path, unique_filename)
            file.save(file_path)

            # 读取文件内容数据
            file_content_type, file_content_type_name = check_file_content_type(file_path)
            print("file_content_type:", file_content_type)
            try:
                drlx = request.args.get('lx')
            except Exception:
                pass
            if drlx is None or drlx == 'None':
                drlx = 'sd'
            code = load_content_data(file_path, file_content_type, drlx)
            if code == 401:
                return jsonify('文件内容识别失败'), 400
            if code == 400:
                return jsonify('文件内容识别失败，请检查修改后重新上传'), 400
            if code != 200:
                return jsonify('文件内数据识别失败或出现其他未知问题，请检查修改后重新上传'), 400

            con = get_connection()
            # 插入上传记录到数据库
            con.execute(
                "INSERT INTO uploads (id, filename, unique_filename, folder_path, file_type, file_content_type, file_content_type_name) VALUES (?, ?, ?, ?, ?, ?, ?)",
                [uuid4, file.filename, unique_filename, relatively_folder_path, file_extension, file_content_type, file_content_type_name])
            con.close()

            return jsonify({
                'message': '文件上传成功',
                'filename': file.filename,
                'unique_filename': unique_filename,
                'folder_path': folder_path,
                'file_type': file_extension
            }), 200
        else:
            return jsonify('文件格式不支持'), 400

    except Exception as e:
        print(traceback.format_exc())
        return jsonify(str(e)), 500


if __name__ == '__main__':
    init()
